import pandas as pd
import pymysql
import pymysql.cursors

class  MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='localhost',
            user='root',
            password='sjk1234',
            database='tushare',
            port=3306,
            charset='utf8'
        )
        

class classIfcation(object):
    def __init__(self):
        pass
    
    def get_fina_indicator(self, conn):
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        query = """SELECT ts_code,  ann_date, eps, total_revenue_ps, undist_profit_ps, fcff, 
        fcfe, tangible_asset, bps, grossprofit_margin, npta FROM 
        financial_data WHERE financial_data.ann_date BETWEEN '2023-01-01' AND '2024-01-01'"""
        cursor.execute(query)
        result = cursor.fetchall()
        df = pd.DataFrame(result)
        df=df.dropna(subset=['ts_code','ann_date','eps','total_revenue_ps','undist_profit_ps',
                             'fcff','fcfe','tangible_asset','bps','grossprofit_margin','npta'])
        df=df.reset_index(drop=True)
        return df
    
    def get_daily(self, conn, df):
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        new_list = []
        for index, row in df.iterrows():
            print(index)
            ts_code=row['ts_code']
            ann_date=row['ann_date'].strftime('%Y-%m-%d')
            query = f"""
            SELECT trade_date, closes FROM date_1 WHERE date_1.trade_date = '{ann_date}' AND date_1.ts_code = '{ts_code}'
            order by trade_date asc limit 20 """
            cursor.execute(query)
            result = cursor.fetchall()
            df1 = pd.DataFrame(result)
            try:
                if len(df1) >0:
                    max_close = df1['closes'].max()
                    min_close = df1['closes'].min()
                    the_close = df1['closes'].iloc[-1]
                    new_list.append({
                        'ts_code':ts_code,
                        'ann_date':ann_date,
                        'max_close':max_close,
                        'min_close':min_close,
                        'the_close':the_close,
                        'eps':row['eps'],
                        'total_revenue_ps':row['total_revenue_ps'],
                        'undist_profit_ps':row['undist_profit_ps'],
                        'fcff':row['fcff'],
                        'fcfe':row['fcfe'],
                        'tangible_asset':row['tangible_asset'],
                        'bps':row['bps'],
                        'grossprofit_margin':row['grossprofit_margin'],
                        'npta':row['npta'],
                        'gross_margin':row['gross_margin']
                    })
            except Exception as e:
                print(e)
        df2 = pd.DataFrame(new_list)
        df2.to_csv('fina_indicator.csv',index=False)
        
             
if __name__ == '__main__':
    mu=MysqlUtils()
    ci=classIfcation()
    df=ci.get_fina_indicator(mu.conn)
    ci.get_daily(mu.conn,df)